/*
 *  Copyright 2011 yura.
 *
 *  Licensed under the Apache License, Version 2.0 (the "License");
 *  you may not use this file except in compliance with the License.
 *  You may obtain a copy of the License at
 *
 *       http://www.apache.org/licenses/LICENSE-2.0
 *
 *  Unless required by applicable law or agreed to in writing, software
 *  distributed under the License is distributed on an "AS IS" BASIS,
 *  WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 *  See the License for the specific language governing permissions and
 *  limitations under the License.
 *  under the License.
 */

package org.opu.db_vdumper.util;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.SQLWarning;
import java.sql.Statement;
import java.util.logging.Level;
import org.opu.db_vdumper.beans.UserData;

/**
 *  Data receiver from database.
 * @author yura
 */
public class DbManager {
    private UserData prop;
    private boolean connected = false;
    private Connection conn;
    private Statement statement;
    private ResultSet result_set;
    private ResultSetMetaData meta_data;
    private boolean query_sent = false;
    private QueryHandler queryHandler;

    /**
     *  Create new instance with connection to database, according with
     * given user data
     * @param prop connection properties: user data, and database properties
     * @throws ClassNotFoundException if troubles while loading JDBC driver
     * @throws SQLException it database cannot be accessed
     */
    public DbManager(UserData prop) throws ClassNotFoundException, SQLException{
        this.prop = prop;
        String url = getDbUrlString();
        connect(url);
    }

    /**
     *  Create connection to database, according with given URL
     * @param url connection address with additional properties
     * @throws ClassNotFoundException if troubles while loading JDBC driver
     * @throws SQLException it database cannot be accessed
     */
    public final void connect(String url) throws ClassNotFoundException,
            SQLException {
        
        try {
            Class.forName(prop.getDbProperties().getClassName());
            conn = DriverManager.getConnection(url, prop.getUser(), prop.getPassword());
            connected = true;
            queryHandler = new QueryAdapter(this);
        } catch (ClassNotFoundException ex) {
            Logger.getInstance().error("Troubles while loading JDBC driver - "
                    + prop.getDbProperties() + ": " + ex);
            throw ex;
        } catch (SQLException ex) {
            Logger.getInstance().error("Troubles - database cannot be accessed:"
                    + ex);
            throw ex;
        }
    }

    public final String getDbUrlString(){
        return getDbUrlString(prop);
    }

    public static String getDbUrlString(UserData prop){
        if (prop == null){
            return "";
        }

        return new StringBuilder()
                .append("jdbc:")
                .append(prop.getDbProperties().getName())
                .append("://")
                .append(prop.getDbHost())
                .append(":")
                .append(prop.getDbPort())
                .append("/")
                .append(prop.getDbName())
                .toString();
    }

    public UserData getUserData(){
        return prop;
    }

    public boolean isConnected() {
        return connected;
    }

    public int getColumns() {
        if (!query_sent) {
            return 0;
        }

        try {
            return meta_data.getColumnCount();
        } catch (SQLException ex) {
            Logger.getInstance().error(this, "Can't get columns amount");
            Logger.getInstance().error(this, ex);
            return 0;
        }
    }

    public int getRows() {
        if (!query_sent) {
            return 0;
        }

        try {
            result_set.last();
            return result_set.getRow();
        } catch (Exception ex) {
            Logger.getInstance().error(this, "Can't get rows amount");
            Logger.getInstance().error(this, ex);
            return 0;
        }
    }

    /**
     * Returns string value.
     * @param row int - number of row (the first is 1).
     * @param col int - number of column (the first is 1).
     * @return String
     */
    public String getString(int row, int col) {
        try {
            if (!query_sent) {
                return "";
            }
            if (result_set.absolute(row)) {
                return result_set.getString(col);
            }
            return "";
        } catch (Exception ex) {
            Logger.getInstance().error(this, String.format("Can't get string "
                    + "data [%d][%d]", row, col));
            Logger.getInstance().error(this, ex);
            return "";
        }
    }

    /**
     * Returns string value.
     * @param row int - number of row (the first is 1).
     * @param col String - column name.
     * @return String
     */
    public String getString(int row, String col) {
        try {
            if (!query_sent) {
                return "";
            }
            if (result_set.absolute(row)) {
                return result_set.getString(col);
            }
            return "";
        } catch (Exception ex) {
            Logger.getInstance().error(this, String.format("Can't get string "
                    + "data [%d][\"%d\"]", row, col));
            Logger.getInstance().error(this, ex);
            return "";
        }
    }

    public Object getObject(int row, String col) {
        try {
            if (!query_sent) {
                return "";
            }
            if (result_set.absolute(row)) {
                return result_set.getObject(col);
            }
            return "";
        } catch (Exception ex) {
            Logger.getInstance().error(this, String.format("Can't get object "
                    + "[%d][\"%s\"]", row, col));
            Logger.getInstance().error(this, ex);
            return "";
        }
    }

    public Object getObject(int row, int col) {
        try {
            if (!query_sent) {
                return "";
            }
            if (result_set.absolute(row)) {
                return result_set.getObject(col);
            }
            return "";
        } catch (Exception ex) {
            Logger.getInstance().error(this, String.format("Can't get object "
                    + "[%d][%d]", row, col));
            Logger.getInstance().error(this, ex);
            return "";
        }
    }

    /**
     * Returns int value.
     * @param row int - number of row (the first is 1).
     * @param col int - number of column (the first is 1).
     * @return String
     */
    public int getInt(int row, int col) {
        try {
            if (!query_sent) {
                return 0;
            }
            if (result_set.absolute(row)) {
                return result_set.getInt(col);
            }
            return 0;
        } catch (Exception ex) {
            Logger.getInstance().error(this, String.format("Can't get int data "
                    + "[%d][%d]", row, col));
            Logger.getInstance().error(this, ex);
            return 0;
        }

    }

    /**
     * Returns string value.
     * @param row int - number of row (the first is 1).
     * @param col String - column name.
     * @return String
     */
    public int getInt(int row, String col) {
        try {
            if (!query_sent) {
                return 0;
            }
            if (result_set.absolute(row)) {
                return result_set.getInt(col);
            }
            return 0;
        } catch (Exception ex) {
            Logger.getInstance().error(this, String.format("Can't get int data "
                    + "[%d][\"%s\"]", row, col));
            Logger.getInstance().error(this, ex);
            return 0;
        }

    }

    public String[] headers(){
        try {
            int columns = meta_data.getColumnCount();
            String ret[] = new String[columns];
            for (int i = 0; i < columns; i++) {
                ret[i] = meta_data.getColumnLabel(i + 1);
            }
            return ret;
        } catch (SQLException ex){
            Logger.getInstance().error(this, "Can't get headers");
            Logger.getInstance().error(this, ex);
            
            return new String[]{""};
        }
    }

    /**
     * Clears query statement.
     */
    public void clear() {
        try {
            if (query_sent) {
                if (statement != null) {
                    statement.close();
                }
                query_sent = false;
            }
        } catch (SQLException ex) {
            Logger.getInstance().error(this, "Can't clear statement");
            Logger.getInstance().error(this, ex);
            query_sent = false;
        }
    }

    /**
     *  Return true if aouto commit mode set
     * @param autoCommit
     * @return true if aouto commit mode set
     */
    public boolean setAutoCommit(boolean autoCommit) {
        try {
            conn.setAutoCommit(autoCommit);
            return conn.getAutoCommit();
        } catch (SQLException ex) {
            Logger.getInstance().error(this, "Can't set aouto commit - " + ex);
            return false;
        }
    }

    public boolean isAutoCommit(){
        try {
            return conn.getAutoCommit();
        } catch (SQLException ex) {
            Logger.getInstance().error(this, "Can't set aouto commit - " + ex);
            return false;
        }
    }

    /**
     *  Return true if all changes maded since
     * @return true if all changes maded since
     */
    public boolean commit() {
        try {
            conn.commit();
            return true;
        } catch (SQLException ex) {
            Logger.getInstance().error(this, "Can't commit - " + ex);
            return false;
        }
    }

    /**
     *  Return true if undoes all changes made in the current transaction
     * @return true if undoes all changes made in the current transaction
     */
    public boolean rollback(){
        try {
            conn.rollback();
            return true;
        } catch (SQLException ex) {
            Logger.getInstance().error(this, "Can't rollback - " + ex);
            return false;
        }
    }

    public void closeConnect() {
        try {
//            if (connected) {
                rollback();
                conn.close();
                connected = false;
//            }
        } catch (Exception ex) {
            Logger.getInstance().error(this, "Can't close connect - " + ex);
        }
    }

    protected void setMetaDataFromQuery() throws SQLException {
        meta_data = result_set.getMetaData();
        query_sent = true;
    }

    protected void setResultSet(ResultSet resultSet){
        this.result_set = resultSet;
    }

    protected void setStatement(Statement statement){
        this.statement = statement;
    }

    public SQLWarning getWarning() {
        try {
            if (statement != null && !statement.isClosed()) {
                return this.statement.getWarnings();
            } else {
                return null;
            }
        } catch (SQLException ex) {
            Logger.getInstance().error(this, "Can't get warning - " + ex);
            return null;
        }
    }

    public Connection getConnection(){
        return conn;
    }

    public QueryHandler getQueryHandler() {
        return queryHandler;
    }

    @Override
    public String toString() {
        return getDbUrlString();
    }

    @Override
    protected void finalize() throws Throwable {
        closeConnect();
    }
}
